

/**	
	AUTHOR		: GANDHI
	DATE		: 15 SEPT, 2005
	DESCRIPTION	: THIS PROC IS USED TO GET FORECAST,BACKLOG AND SHIPPING DETAILS OF TOP 20 END CUSTOMERS. 

	
	EXEC SP_NEW_SALES_FORECAST_ACTUAL_REPORT '0' 	

*/

			

ALTER         PROC SP_NEW_SALES_FORECAST_ACTUAL_REPORT(

	@defaultPrompt varchar(50))


 
 AS
 BEGIN
	
	
	DECLARE @RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE FLOAT, CONFIDENCE_LEVEL FLOAT, BL_QUANTITY INT, BL_PRICE FLOAT, ACTUAL_QUANTITY INT, ACTUAL_PRICE FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT  )
	DECLARE @SPERSON_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE FLOAT , CONFIDENCE_LEVEL FLOAT, BL_QUANTITY INT, BL_PRICE FLOAT, ACTUAL_QUANTITY INT, ACTUAL_PRICE FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )
	DECLARE @BL_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, BL_QUANTITY INT, BL_PRICE FLOAT, CONFIDENCE_LEVEL FLOAT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )	
	DECLARE @BL_TEMP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, BL_QUANTITY INT, BL_PRICE FLOAT, CONFIDENCE_LEVEL FLOAT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )
	DECLARE @SHIP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, ACTUAL_QUANTITY INT, ACTUAL_PRICE FLOAT, CONFIDENCE_LEVEL FLOAT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )	
	DECLARE @SHIP_TEMP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, ACTUAL_QUANTITY INT, ACTUAL_PRICE FLOAT, CONFIDENCE_LEVEL FLOAT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )	
	DECLARE @TOTAL_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE FLOAT, CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )	
	DECLARE @TEMP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE FLOAT, CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )
	DECLARE @RMA_RESULTS TABLE( RMA_PART_NUMBER VARCHAR(100), RMA_DATE DATETIME, RMA_AMOUNT FLOAT, RMA_STATUS VARCHAR(20), RMA_TYPE VARCHAR(10), RMA_QUANTITY INT, RMA_PRICE FLOAT, RMA_END_CUSTOMER VARCHAR(100), QUARTER_START INT, QUARTER_END INT)
	DECLARE @TOP_RESULTS TABLE( ODM VARCHAR(100), FC_PRICE FLOAT, BACKLOG_SHIPPED_PRICE FLOAT, QUARTER_START INT, QUARTER_END INT, TOP_LIST INT )		
	

	DECLARE @START_MONTH INT 
	DECLARE @END_MONTH INT
	DECLARE @QUARTER INT
	SET @QUARTER = DATEPART(QQ,GETDATE())
	DECLARE @YEAR INT
	SET @YEAR = DATEPART(YY,GETDATE())

	/*IF @PERIOD IS NOT NULL AND LTRIM(RTRIM(@PERIOD)) = 'byQuarter'
	BEGIN*/
		IF @QUARTER = 1
		BEGIN
			SET @START_MONTH = 0
			SET @END_MONTH = 2
		END
		ELSE IF @QUARTER = 2
		BEGIN
			SET @START_MONTH = 3
			SET @END_MONTH = 5
		END
		ELSE IF @QUARTER = 3
		BEGIN
			SET @START_MONTH = 6
			SET @END_MONTH = 8
		END
		ELSE IF @QUARTER = 4
		BEGIN
			SET @START_MONTH = 9
			SET @END_MONTH = 11
		END
		ELSE
		BEGIN
			SET @START_MONTH = 0
			SET @END_MONTH = 11
		END
	--END
	/*ELSE IF @PERIOD IS NOT NULL AND LTRIM(RTRIM(@PERIOD)) = 'byMonth'
	BEGIN
		SET @START_MONTH   = 	@FROM_MONTH
		SET @END_MONTH 	   = 	@TO_MONTH
	END*/

	DECLARE @SF_SPID INT
	DECLARE @SF_SPERSON_NAME VARCHAR(100)
	DECLARE @SF_CUSTOMER VARCHAR(100)
 	DECLARE @SF_PRODUCT VARCHAR(50)
 	DECLARE @SF_PROJECT VARCHAR(50)
 	DECLARE @SF_ODM VARCHAR(100)
	DECLARE @SF_CUSTOMER_NAME VARCHAR(100)
 	DECLARE @SF_PRODUCT_NAME VARCHAR(50)
 	DECLARE @SF_PROJECT_NAME VARCHAR(50)
 	DECLARE @SF_ODM_NAME VARCHAR(100)
 	DECLARE @SF_MONTH INT
	DECLARE @SF_QUARTER INT
 	DECLARE @SF_YEAR INT
 	DECLARE @SF_QUANTITY INT
	DECLARE @SF_PRICE FLOAT
	DECLARE @SF_CONFIDENCE_LEVEL FLOAT
	--DECLARE @MKTG_PART_NUMBER VARCHAR(100)
	
	DECLARE @SPERSON_SPID INT
	DECLARE @LEVEL_ORDER INT
	DECLARE @QUARTER_START INT
	DECLARE @QUARTER_END INT

 	DECLARE @SPERSON_CURSOR CURSOR 
	
	DECLARE @CUSTOMER_INPUT VARCHAR(100)
	DECLARE @PROJECT_INPUT VARCHAR(100)
	SET @CUSTOMER_INPUT = NULL
	SET @PROJECT_INPUT = NULL

	DECLARE @ODM_INPUT VARCHAR(100) 
 	DECLARE @PRODUCT_INPUT VARCHAR(100)
 	DECLARE @SPERSON_INPUT VARCHAR(100)
	SET @ODM_INPUT = NULL
	SET @PRODUCT_INPUT = NULL
	SET @SPERSON_INPUT = NULL	

	IF @CUSTOMER_INPUT IS NULL  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = ''  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-1'  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-2' 
	BEGIN	
		SET @CUSTOMER_INPUT = '0'
	END
	IF @PROJECT_INPUT IS NULL  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = ''  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-2'
	BEGIN	
		SET @PROJECT_INPUT = '0'
	END
	IF @PRODUCT_INPUT IS NULL  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = ''  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-2'
	BEGIN
		SET @PRODUCT_INPUT = '0'
	END
	IF @ODM_INPUT IS NULL  OR  LTRIM(RTRIM(@ODM_INPUT)) = ''  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-1'  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-2'
	BEGIN
		SET @ODM_INPUT = '0'
	END	
	IF @SPERSON_INPUT IS NULL  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = ''  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = '-1'  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = '-2'
	BEGIN
		SET @SPERSON_INPUT = '0'
	END	

	DECLARE @PART_NUMBER VARCHAR(100)
	DECLARE @END_CUSTOMER VARCHAR(100)
	DECLARE @SALES_PERSON VARCHAR(100)

	IF @PRODUCT_INPUT <> '0'
	BEGIN
		SELECT @PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_INPUT
	END
	IF @ODM_INPUT <> '0'
	BEGIN
		SELECT @END_CUSTOMER = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @ODM_INPUT
	END
	IF @SPERSON_INPUT <> '0'
	BEGIN
		SELECT @SALES_PERSON = SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = @SPERSON_INPUT
	END

 	DECLARE @SQLSTRING NVARCHAR(3000)
	DECLARE @PARAMDEFINITION NVARCHAR(500)
	
		SET @SQLSTRING = N'DECLARE FORECASTCURSOR CURSOR FOR SELECT SPERSONID, CUSTID, PROJID, PRODID, ODMID, MONTH, QUARTER, YEAR, QTY, QTY*PRICE, CONFIDENCE_LEVEL FROM FCENTRYMASTER WHERE ROWID IS NOT NULL '
		SET @PARAMDEFINITION = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @START_MONTH INT, @END_MONTH INT, @YEAR INT'
		
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END
		IF @SPERSON_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SPERSONID = @PRM_SPERSON_INPUT '
		END	
				
		SET @SQLSTRING = @SQLSTRING + ' AND ( MONTH >= @START_MONTH AND YEAR = @YEAR AND MONTH <= @END_MONTH ) '	

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT,  @PRM_SPERSON_INPUT = @SPERSON_INPUT, @START_MONTH = @START_MONTH, @END_MONTH = @END_MONTH, @YEAR = @YEAR
	
	SET @SQLSTRING = NULL
	SET @PARAMDEFINITION = NULL
	
		SET @SQLSTRING = N'DECLARE SPERSON_CURSOR CURSOR FOR SELECT DISTINCT FCSPERSONREL.SPERSONID, LEVELORDER FROM FCSPERSONREL WHERE 
				   FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE ROWID IS NOT NULL'
		SET @PARAMDEFINITION = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100)'
		
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END
		IF @SPERSON_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SPERSONID = @PRM_SPERSON_INPUT '
		END	
				
		SET @SQLSTRING = @SQLSTRING + ' ) ORDER BY LEVELORDER DESC, SPERSONID '	

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT,  @PRM_SPERSON_INPUT = @SPERSON_INPUT
	
	
		OPEN FORECASTCURSOR 
 			FETCH NEXT FROM FORECASTCURSOR
 			INTO @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_CONFIDENCE_LEVEL
 			WHILE @@FETCH_STATUS = 0
 			BEGIN

				SET @SF_CUSTOMER_NAME = NULL
				SET @SF_PROJECT_NAME = NULL
				SET @SF_PRODUCT_NAME = NULL
				SET @SF_ODM_NAME = NULL
				SET @SF_SPERSON_NAME = NULL
				SELECT @SF_CUSTOMER_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_CUSTOMER
				SELECT @SF_PROJECT_NAME = PROJNAME FROM  PROJSETUP WHERE PROJID = @SF_PROJECT
				SELECT @SF_PRODUCT_NAME = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @SF_PRODUCT
				SELECT @SF_ODM_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_ODM
				SELECT @SF_SPERSON_NAME = SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = @SF_SPID

				IF (@SF_MONTH >=0 AND @SF_MONTH <=2)
	  			BEGIN
	  				SET @QUARTER_START = 1
	  				SET @QUARTER_END =3
	  			END
	  			ELSE IF (@SF_MONTH >=3 AND @SF_MONTH <=5)
	  			BEGIN
	  				SET @QUARTER_START = 2
	  				SET @QUARTER_END =6
	 			END
	  			ELSE IF (@SF_MONTH >=6 AND @SF_MONTH <=8)
	  			BEGIN
	  				SET @QUARTER_START = 3
	  				SET @QUARTER_END =9
	  			END						
	  			ELSE IF (@SF_MONTH >=9 AND @SF_MONTH <=11)
	  			BEGIN
	  				SET @QUARTER_START = 4
	  				SET @QUARTER_END =12
	   			END
 				
				INSERT INTO @RESULTS VALUES(  @SF_SPID, @SF_CUSTOMER_NAME, @SF_PROJECT_NAME, @SF_PRODUCT_NAME, @SF_ODM_NAME, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_CONFIDENCE_LEVEL, NULL, NULL, NULL, NULL, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_SPERSON_NAME, @QUARTER_START, @QUARTER_END )
 					
				--SELECT @MKTG_PART_NUMBER = PRODUCT.P_MARKETING_PART_NUMBER FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.PRODUCT_ID = @SF_PRODUCT AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID  
				--UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @SF_PRODUCT_NAME
 				
				SET @SF_SPID = NULL
				SET @SF_CUSTOMER = NULL
				SET @SF_PROJECT = NULL
				SET @SF_PRODUCT = NULL
				SET @SF_ODM = NULL
				SET @SF_MONTH = NULL
				SET @SF_QUARTER = NULL
				SET @SF_YEAR = NULL
				SET @SF_QUANTITY = NULL
				SET @SF_PRICE = NULL
				SET @SF_CONFIDENCE_LEVEL = NULL
	
 				FETCH NEXT FROM FORECASTCURSOR
 				INTO @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_CONFIDENCE_LEVEL				
 			END	

 		CLOSE FORECASTCURSOR
 		DEALLOCATE FORECASTCURSOR 
		
			
			OPEN SPERSON_CURSOR 
	 			FETCH NEXT FROM SPERSON_CURSOR
	 			INTO @SPERSON_SPID, @LEVEL_ORDER
	 			WHILE @@FETCH_STATUS = 0
	 			BEGIN
	
					INSERT INTO @SPERSON_RESULTS SELECT * FROM @RESULTS WHERE SPERSON_ID = @SPERSON_SPID
					
					SET @SPERSON_SPID = NULL 										
	 				FETCH NEXT FROM SPERSON_CURSOR
	 				INTO @SPERSON_SPID, @LEVEL_ORDER
	 			END	
				INSERT INTO @TOTAL_RESULTS SELECT  SPERSON_ID, CUSTOMER, PROJECT, PRODUCT, ODM, MONTH, QUARTER, YEAR, SUM(FC_QUANTITY) FC_QUANTITY, SUM(FC_PRICE) FC_PRICE, AVG(CONFIDENCE_LEVEL) CONFIDENCE_LEVEL, CUSTID, PROJID, PRODID, ODMID, SALES_PERSON, QUARTER_START, QUARTER_END FROM @SPERSON_RESULTS				
				GROUP BY SPERSON_ID, CUSTOMER, CUSTID, PROJECT, PROJID, PRODUCT, PRODID, ODM, ODMID, SALES_PERSON, MONTH, QUARTER, QUARTER_START, QUARTER_END, YEAR
				INSERT INTO @TEMP_RESULTS SELECT NULL, NULL, NULL, PRODUCT, ODM, MONTH, NULL, YEAR, SUM(FC_QUANTITY) FC_QUANTITY, SUM(FC_PRICE) FC_PRICE, AVG(CONFIDENCE_LEVEL) CONFIDENCE_LEVEL, NULL, NULL, PRODID, ODMID, NULL, QUARTER_START, QUARTER_END FROM @TOTAL_RESULTS				
				GROUP BY ODM, PRODUCT, ODMID, PRODID, MONTH, YEAR, QUARTER_START, QUARTER_END
				DELETE FROM @RESULTS
				INSERT INTO @RESULTS SELECT SPERSON_ID, CUSTOMER, PROJECT, PRODUCT, ODM, MONTH, QUARTER, YEAR, FC_QUANTITY, FC_PRICE, CONFIDENCE_LEVEL, NULL, NULL, NULL, NULL, CUSTID, PROJID, PRODID, ODMID, SALES_PERSON, QUARTER_START, QUARTER_END FROM @TEMP_RESULTS																								

	 		CLOSE SPERSON_CURSOR
	 		DEALLOCATE SPERSON_CURSOR 


		-- FOR BACKLOG DETAILS

		DECLARE @CURRENT_DATE DATETIME
		SET @CURRENT_DATE = CONVERT(DATETIME,CURRENT_TIMESTAMP)
		DECLARE @BL_CUSTOMER VARCHAR(100)
		DECLARE @BL_END_USER VARCHAR(100)
	 	DECLARE @BL_PRODUCT VARCHAR(50)
		DECLARE @BL_MONTH INT
		DECLARE @BL_QUARTER INT
	 	DECLARE @BL_YEAR INT
	 	DECLARE @BL_QUANTITY INT
		DECLARE @OLD_QUANTITY INT
		DECLARE @BL_PRICE FLOAT
		DECLARE @OLD_PRICE FLOAT
		DECLARE @ROW_COUNT INT
		DECLARE @FC_CUSTOMER VARCHAR(100)
		DECLARE @BL_SALES_PERSON VARCHAR(100)
		DECLARE @BL_REQ_OR_SCH_DATE DATETIME
		DECLARE @BL_AMOUNT FLOAT
		DECLARE @BL_STATUS  VARCHAR(50)
		DECLARE @LINEITEM_ID INT
		DECLARE @REVISED_DATE DATETIME
		DECLARE @SHIPPED_QTY INT
		SET @QUARTER_START = NULL
		SET @QUARTER_END = NULL

		DECLARE @BACKLOGSTRING NVARCHAR(3000)
		DECLARE @BACKLOGPARAMS NVARCHAR(500)

		SET @BACKLOGSTRING = N'DECLARE BL_CURSOR CURSOR FOR SELECT C.C_NAME, P.PART_NUMBER, ISNULL( COOL.COOL_SCHEDULE_DATE, CURRENT_TIMESTAMP ) as COOL_SCHEDULE_DATE, COOL.COOL_AMOUNT AS AMOUNT, COOL.COOL_STATUS, LINEITEM_ID, 
		 			COOL_REVISED_DATE, COOL_QTY, COOL_UNIT_COST, CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 OR SO.SO_ODM_ID = 0 OR SO.SO_ODM_ID = '''' ) THEN C.C_Name
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
					END AS END_USER, SO.SO_SALES_PERSON AS SALES_PERSON
		 			FROM PRODUCT P,CUSTOMER_OPEN_ORDER_LINEITEM COOL,SALES_ORDER SO,CUSTOMER C WHERE COOL.PRODUCT_ID=P.PRODUCT_ID 
					AND COOL.SALESORDER_ID=SO.SALESORDER_ID AND SO.CUSTOMER_ID=C.CUSTOMER_ID
					AND SO.SO_IS_SAMPLE = 1 AND COOL_STATUS <> ''SHIPPED'''

		SET @BACKLOGPARAMS = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @START_MONTH INT, @END_MONTH INT, @YEAR INT'
		
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @BACKLOGSTRING = @BACKLOGSTRING + ' AND PART_NUMBER = @PRM_PRODUCT_INPUT '
		END
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @BACKLOGSTRING = @BACKLOGSTRING + ' AND (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 OR SO.SO_ODM_ID = 0 OR SO.SO_ODM_ID = '''' ) THEN C.C_Name
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
					END) = @PRM_ODM_INPUT '
		END
		IF @SPERSON_INPUT <> '0'
		BEGIN
			SET @BACKLOGSTRING = @BACKLOGSTRING + ' AND SO_SALES_PERSON = @PRM_SPERSON_INPUT '
		END	

		EXECUTE SP_EXECUTESQL @BACKLOGSTRING, @BACKLOGPARAMS, @PRM_PRODUCT_INPUT = @PART_NUMBER, @PRM_ODM_INPUT = @END_CUSTOMER,  @PRM_SPERSON_INPUT = @SALES_PERSON, @START_MONTH = @START_MONTH, @END_MONTH = @END_MONTH, @YEAR = @YEAR

			
 			OPEN BL_CURSOR 
 				FETCH NEXT FROM BL_CURSOR
 				INTO @BL_CUSTOMER, @BL_PRODUCT, @BL_REQ_OR_SCH_DATE, @BL_AMOUNT, @BL_STATUS, @LINEITEM_ID, @REVISED_DATE, @BL_QUANTITY, @BL_PRICE, @BL_END_USER, @BL_SALES_PERSON  
 				WHILE @@FETCH_STATUS = 0
 				BEGIN	

					IF(@REVISED_DATE IS NOT NULL OR @REVISED_DATE <> '')
					BEGIN
						SET @BL_REQ_OR_SCH_DATE = @REVISED_DATE
						
						IF( @BL_REQ_OR_SCH_DATE < @CURRENT_DATE )
						BEGIN
							SET @BL_REQ_OR_SCH_DATE = @CURRENT_DATE
						END
						ELSE
						BEGIN
							SET @BL_REQ_OR_SCH_DATE = @BL_REQ_OR_SCH_DATE
						END
					END						
					ELSE
					BEGIN
						IF( @BL_REQ_OR_SCH_DATE < @CURRENT_DATE )
						BEGIN
							SET @BL_REQ_OR_SCH_DATE = @CURRENT_DATE
						END
						ELSE
						BEGIN
							SET @BL_REQ_OR_SCH_DATE = @BL_REQ_OR_SCH_DATE
						END
					END							


						SET @BL_MONTH = DATEPART(MM,@BL_REQ_OR_SCH_DATE)-1
						SET @BL_YEAR  = YEAR(@BL_REQ_OR_SCH_DATE)

								IF ( @BL_QUANTITY IS NULL)
								BEGIN
									SET @BL_QUANTITY = 0
								END
								IF ( @BL_PRICE IS NULL)
								BEGIN
									SET @BL_PRICE = 0.0
								END
							
								IF(@BL_AMOUNT IS NULL)
								BEGIN
									SET @BL_AMOUNT = 0
								END
								ELSE
								BEGIN
									SET @BL_AMOUNT = @BL_QUANTITY * @BL_PRICE
								END				
								
								SELECT @SHIPPED_QTY = ISNULL(SUM(CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = @LINEITEM_ID AND CSLIT_STATUS = 'SHIPPED'
								
								SET @BL_AMOUNT = @BL_AMOUNT - (@BL_PRICE * @SHIPPED_QTY) 
								
								IF ( @BL_AMOUNT < 0 )
								BEGIN
									SET @BL_AMOUNT = 0 
								END
	
								SET @BL_QUANTITY = @BL_QUANTITY - @SHIPPED_QTY
								IF ( @BL_QUANTITY < 0 )
								BEGIN
									SET @BL_QUANTITY = 0
								END

	 					SELECT @ROW_COUNT = COUNT(*) FROM @BL_RESULTS
	 					WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR 
	 
	 						IF @ROW_COUNT = 0 
	 					        BEGIN
								IF (@BL_MONTH >=0 AND @BL_MONTH <=2)
					  			BEGIN
					  				SET @QUARTER_START = 1
					  				SET @QUARTER_END =3
					  			END
					  			ELSE IF (@BL_MONTH >=3 AND @BL_MONTH <=5)
					  			BEGIN
					  				SET @QUARTER_START = 2
					  				SET @QUARTER_END =6
					 			END
					  			ELSE IF (@BL_MONTH >=6 AND @BL_MONTH <=8)
					  			BEGIN
					  				SET @QUARTER_START = 3
					  				SET @QUARTER_END =9
					  			END						
					  			ELSE IF (@BL_MONTH >=9 AND @BL_MONTH <=11)
					  			BEGIN
					  				SET @QUARTER_START = 4
					  				SET @QUARTER_END =12
					   			END

	 							INSERT INTO @BL_RESULTS VALUES( 0, @BL_CUSTOMER, '0', @BL_PRODUCT, @BL_END_USER, @BL_MONTH, @BL_QUARTER, @BL_YEAR, @BL_QUANTITY, @BL_AMOUNT, 0.0, @BL_SALES_PERSON, @QUARTER_START, @QUARTER_END )
	 						END
	 						ELSE
	 						BEGIN
	 							SELECT @OLD_QUANTITY = BL_QUANTITY, @OLD_PRICE = BL_PRICE  FROM @BL_RESULTS
	 							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR 
	 							
	 	
	 							UPDATE @BL_RESULTS SET BL_QUANTITY = (@BL_QUANTITY + @OLD_QUANTITY), BL_PRICE = @OLD_PRICE +  ( @BL_QUANTITY*@BL_PRICE )
	 							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER  AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR 
							END
							
							SET @BL_CUSTOMER = NULL
							SET @BL_PRODUCT = NULL
							SET @BL_MONTH = NULL
							SET @BL_QUARTER = NULL
							SET @BL_YEAR = NULL
							SET @BL_QUANTITY = NULL
							SET @BL_PRICE = NULL
							SET @BL_END_USER = NULL
							SET @BL_SALES_PERSON = NULL 	
							SET @BL_REQ_OR_SCH_DATE = NULL
							SET @BL_AMOUNT = NULL
							SET @BL_STATUS = NULL
							SET @LINEITEM_ID = NULL
							SET @REVISED_DATE = NULL
							SET @SHIPPED_QTY = NULL
							 			 														
					FETCH NEXT FROM BL_CURSOR
					INTO @BL_CUSTOMER, @BL_PRODUCT, @BL_REQ_OR_SCH_DATE, @BL_AMOUNT, @BL_STATUS, @LINEITEM_ID, @REVISED_DATE, @BL_QUANTITY, @BL_PRICE, @BL_END_USER, @BL_SALES_PERSON  
				END					
					INSERT INTO @BL_TEMP_RESULTS SELECT NULL,NULL,NULL,PRODUCT,ODM,MONTH,NULL,YEAR,SUM(BL_QUANTITY),SUM(BL_PRICE),AVG(CONFIDENCE_LEVEL),NULL, QUARTER_START, QUARTER_END
					FROM @BL_RESULTS GROUP BY ODM,PRODUCT,MONTH,YEAR,QUARTER_START,QUARTER_END
 					
 			CLOSE BL_CURSOR
 			DEALLOCATE BL_CURSOR

				INSERT INTO @RESULTS SELECT  NULL, NULL, 'NO PROJECT', PRODUCT, ODM, MONTH, NULL, YEAR, NULL, NULL, NULL, BL_QUANTITY, BL_PRICE, NULL, NULL, NULL, NULL, NULL, NULL, NULL, QUARTER_START, QUARTER_END FROM @BL_TEMP_RESULTS
				WHERE MONTH >= @START_MONTH AND YEAR = @YEAR AND MONTH <= @END_MONTH


	-- FOR SHIP DETAILS

	DECLARE @SHIP_CUSTOMER VARCHAR(100)
	DECLARE @SHIP_END_USER VARCHAR(100)
 	DECLARE @SHIP_PRODUCT VARCHAR(50)
	DECLARE @SHIP_SALES_PERSON VARCHAR(100)
	DECLARE @SHIP_MONTH INT
	DECLARE @SHIP_QUARTER INT
 	DECLARE @SHIP_YEAR INT
 	DECLARE @SHIP_QUANTITY INT
	DECLARE @SHIP_OLD_QUANTITY INT
	DECLARE @SHIP_PRICE FLOAT
	DECLARE @SHIP_OLD_PRICE FLOAT
	DECLARE @SHIP_ROW_COUNT INT	
	SET @QUARTER_START = NULL
	SET @QUARTER_END = NULL


	DECLARE @SHIPSTRING NVARCHAR(3000)
	DECLARE @SHIPPARAMS NVARCHAR(500)

	SET @SHIPSTRING = N'DECLARE SHIP_CURSOR CURSOR FOR SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SHIPMONTH, SHIPQUARTER, SHIPYEAR, QUANTITY, UNIT_PRICE
	 		FROM FN_SHIPPED_ORDERS_FOR_FORECAST_REPORT( ''0'', ''0'') WHERE 1=1'
	SET @SHIPPARAMS = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @START_MONTH INT, @END_MONTH INT, @YEAR INT'

	IF @PRODUCT_INPUT <> '0'
	BEGIN
		SET @SHIPSTRING = @SHIPSTRING + ' AND PRODUCT = @PRM_PRODUCT_INPUT '
	END
	IF @ODM_INPUT <> '0'
	BEGIN
		SET @SHIPSTRING = @SHIPSTRING + ' AND END_USER = @PRM_ODM_INPUT '
	END
	IF @SPERSON_INPUT <> '0'
	BEGIN
		SET @SHIPSTRING = @SHIPSTRING + ' AND SALES_PERSON = @PRM_SPERSON_INPUT '
	END	
			
	SET @SHIPSTRING = @SHIPSTRING + ' AND ( SHIPMONTH >= @START_MONTH AND SHIPYEAR = @YEAR AND SHIPMONTH <= @END_MONTH ) '	
	
	EXECUTE SP_EXECUTESQL @SHIPSTRING, @SHIPPARAMS, @PRM_PRODUCT_INPUT = @PART_NUMBER, @PRM_ODM_INPUT = @END_CUSTOMER,  @PRM_SPERSON_INPUT = @SALES_PERSON, @START_MONTH = @START_MONTH, @END_MONTH = @END_MONTH, @YEAR = @YEAR


	 	OPEN SHIP_CURSOR	
	 		FETCH NEXT FROM SHIP_CURSOR
	 		INTO @SHIP_CUSTOMER, @SHIP_PRODUCT, @SHIP_SALES_PERSON, @SHIP_END_USER, @SHIP_MONTH, @SHIP_QUARTER, @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_PRICE
	
			WHILE @@FETCH_STATUS = 0
 			BEGIN	
	
 					SELECT @SHIP_ROW_COUNT = COUNT(*) FROM @SHIP_RESULTS
 					WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR 
 					
 						IF @SHIP_ROW_COUNT = 0 
 					        BEGIN
 							IF (@SHIP_MONTH >=0 AND @SHIP_MONTH <=2)
				  			BEGIN
				  				SET @QUARTER_START = 1
				  				SET @QUARTER_END =3
				  			END
				  			ELSE IF (@SHIP_MONTH >=3 AND @SHIP_MONTH <=5)
				  			BEGIN
				  				SET @QUARTER_START = 2
				  				SET @QUARTER_END =6
				 			END
				  			ELSE IF (@SHIP_MONTH >=6 AND @SHIP_MONTH <=8)
				  			BEGIN
				  				SET @QUARTER_START = 3
				  				SET @QUARTER_END =9
				  			END						
				  			ELSE IF (@SHIP_MONTH >=9 AND @SHIP_MONTH <=11)
				  			BEGIN
				  				SET @QUARTER_START = 4
				  				SET @QUARTER_END =12
				   			END
							
							INSERT INTO @SHIP_RESULTS VALUES( 0, @SHIP_CUSTOMER, '0', @SHIP_PRODUCT, @SHIP_END_USER, @SHIP_MONTH, 0 , @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_QUANTITY*@SHIP_PRICE, 0.0, @SHIP_SALES_PERSON, @QUARTER_START, @QUARTER_END )
 						END
 						ELSE
 						BEGIN
 							SELECT @SHIP_OLD_QUANTITY = ACTUAL_QUANTITY, @SHIP_OLD_PRICE = ACTUAL_PRICE  FROM @SHIP_RESULTS
 							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR 
 							
 	
 							UPDATE @SHIP_RESULTS SET ACTUAL_QUANTITY = (@SHIP_QUANTITY + @SHIP_OLD_QUANTITY), ACTUAL_PRICE = @SHIP_OLD_PRICE +  ( @SHIP_QUANTITY*@SHIP_PRICE )
 							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT  AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR 
						END
							
							SET @SHIP_CUSTOMER = NULL
							SET @SHIP_PRODUCT = NULL
							SET @SHIP_SALES_PERSON = NULL
							SET @SHIP_END_USER = NULL
							SET @SHIP_MONTH = NULL
							SET @SHIP_QUARTER = NULL
							SET @SHIP_YEAR = NULL
							SET @SHIP_QUANTITY = NULL
							SET @SHIP_PRICE = NULL
				FETCH NEXT FROM SHIP_CURSOR
	 			INTO @SHIP_CUSTOMER, @SHIP_PRODUCT, @SHIP_SALES_PERSON, @SHIP_END_USER, @SHIP_MONTH, @SHIP_QUARTER, @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_PRICE
			END 			
				INSERT INTO @SHIP_TEMP_RESULTS SELECT NULL,NULL,NULL,PRODUCT,ODM,MONTH,NULL,YEAR,SUM(ACTUAL_QUANTITY),SUM(ACTUAL_PRICE),AVG(CONFIDENCE_LEVEL),NULL,QUARTER_START,QUARTER_END
				FROM @SHIP_RESULTS GROUP BY ODM,PRODUCT,MONTH,YEAR,QUARTER_START,QUARTER_END

	 	CLOSE SHIP_CURSOR
	 	DEALLOCATE SHIP_CURSOR	
	
			INSERT INTO @RESULTS SELECT  NULL, NULL, 'NO PROJECT', PRODUCT, ODM, MONTH, NULL, YEAR, NULL, NULL, NULL, NULL, NULL, ACTUAL_QUANTITY, ACTUAL_PRICE, NULL, NULL, NULL, NULL, NULL,QUARTER_START,QUARTER_END FROM @SHIP_TEMP_RESULTS
			WHERE MONTH >= @START_MONTH AND YEAR = @YEAR AND MONTH <= @END_MONTH


	--UPDATION OF PART NUMBER WITH CORRESPONDING MARKETING PART NUMBER
	/*SET @SF_PRODUCT = NULL
	OPEN FORECASTCURSOR 
 			FETCH NEXT FROM FORECASTCURSOR
 			INTO @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_CONFIDENCE_LEVEL
 			WHILE @@FETCH_STATUS = 0
 			BEGIN

				SET @SF_PRODUCT_NAME = NULL
				
				SELECT @SF_PRODUCT_NAME = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @SF_PRODUCT
				 					
				SELECT @MKTG_PART_NUMBER = PRODUCT.P_MARKETING_PART_NUMBER FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.PRODUCT_ID = @SF_PRODUCT AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID  
				UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @SF_PRODUCT_NAME
 					
 				FETCH NEXT FROM FORECASTCURSOR
 				INTO @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_CONFIDENCE_LEVEL				
 			END	
 		 																
 	CLOSE FORECASTCURSOR
 	DEALLOCATE FORECASTCURSOR 
	*/	

	--FOR TOP 20 END CUSTOMERS
	/*INSERT INTO @FINAL_RESULTS SELECT ODM, MONTH, YEAR, FC_QUANTITY, FC_PRICE, BL_QUANTITY, BL_PRICE, ACTUAL_QUANTITY, ACTUAL_PRICE, 0.0, QUARTER_START, QUARTER_END, 0	
	FROM @RESULTS

	INSERT INTO @TOP_RESULTS SELECT TOP 20 ODM, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0
	FROM @RESULTS GROUP BY ODM, QUARTER_START, QUARTER_END ORDER BY TOTAL DESC
	
	INSERT INTO @TOP_RESULTS SELECT ODM, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0.0, QUARTER_START, QUARTER_END, 1
	FROM @RESULTS WHERE ODM NOT IN (SELECT ODM FROM @TOP_RESULTS) GROUP BY ODM, QUARTER_START, QUARTER_END

	DECLARE @ODM_NAME VARCHAR(100)
	DECLARE @QSTART INT
	DECLARE @QEND INT
	DECLARE @TOP_LIST INT
	DECLARE @TOTAL_BACKLOG_SHIPPED FLOAT

	DECLARE @TOP_CURSOR CURSOR
	SET @TOP_CURSOR = CURSOR FOR SELECT ODM, QUARTER_START, QUARTER_END, TOP_LIST, TOTAL_BACKLOG_SHIPPED FROM @TOP_RESULTS

	OPEN @TOP_CURSOR 
 			FETCH NEXT FROM @TOP_CURSOR
 			INTO @ODM_NAME, @QSTART, @QEND, @TOP_LIST, @TOTAL_BACKLOG_SHIPPED
 			WHILE @@FETCH_STATUS = 0
 			BEGIN
				
				INSERT INTO @FINAL_TOP_RESULTS SELECT ODM, MONTH, YEAR, SUM(ISNULL(FC_QUANTITY,0)) FC_QUANTITY, SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_QUANTITY,0)) BL_QUANTITY, SUM(ISNULL(BL_PRICE,0.0)) BL_PRICE, SUM(ISNULL(ACTUAL_QUANTITY,0)) ACTUAL_QUANTITY, SUM(ISNULL(ACTUAL_PRICE,0.0)) ACTUAL_PRICE, 0.0, QUARTER_START, QUARTER_END, TOP_LIST
				FROM @FINAL_RESULTS WHERE ODM = @ODM_NAME
				GROUP BY ODM, MONTH, YEAR, QUARTER_START, QUARTER_END, TOP_LIST

				UPDATE @FINAL_TOP_RESULTS SET TOP_LIST = @TOP_LIST, TOTAL_BACKLOG_SHIPPED = @TOTAL_BACKLOG_SHIPPED WHERE ODM = @ODM_NAME AND QUARTER_START = @QSTART AND QUARTER_END = @QEND 				
 					
				SET @ODM_NAME = NULL
				SET @QSTART = NULL
				SET @QEND = NULL
				SET @TOP_LIST = NULL
				SET @TOTAL_BACKLOG_SHIPPED = NULL			
		
 				FETCH NEXT FROM @TOP_CURSOR
 				INTO @ODM_NAME, @QSTART, @QEND, @TOP_LIST, @TOTAL_BACKLOG_SHIPPED
 			END	
 		 																
 	CLOSE @TOP_CURSOR
 	DEALLOCATE @TOP_CURSOR 
	
	SELECT * FROM @FINAL_TOP_RESULTS ORDER BY TOTAL_BACKLOG_SHIPPED DESC, ODM*/
	
	DECLARE @TOP_FC_PRICE FLOAT
	SET @TOP_FC_PRICE = 0.0
	DECLARE @TOP_BACKLOG_SHIPPED_PRICE FLOAT
	SET @TOP_BACKLOG_SHIPPED_PRICE = 0.0 
	
	INSERT INTO @TOP_RESULTS SELECT 'F5 Networks', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'F5%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Sonicwall', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'Sonic%' OR ODM = 'FLASH CHINA' OR ( ODM LIKE 'SERCOMM%' AND PRODUCT LIKE 'SW%') GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Sercomm', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'SERCOMM%' AND PRODUCT NOT LIKE 'SW%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Yamaha', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'YAMAHA%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Cisco', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'Cisco%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Aruba Networks', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'Aruba%' OR ODM = 'FLEXTRONICS SHANGHAI' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'NetScaler', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'NetScaler%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Furukawa', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'FURUKAWADENKI%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Juniper', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'JUNIPER%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Nokia', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'NOKIA%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Turbocom', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'TURBOCOMM%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Array Networks', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'Array%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Symantec', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'SYMANTEC%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Gigabyte', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'GIGABYTE%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Cameo', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'CAMEO%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Huawei', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'Huawei%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Sun Micro', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'SUN%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'McAfee', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'MCAFEE%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Fujitsu', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'Fujitsu%' GROUP BY QUARTER_START, QUARTER_END
	INSERT INTO @TOP_RESULTS SELECT 'Delta', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE, SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 0 FROM @RESULTS WHERE ODM LIKE 'Delta%' GROUP BY QUARTER_START, QUARTER_END

	SELECT @TOP_FC_PRICE = SUM(ISNULL(FC_PRICE,0.0)),@TOP_BACKLOG_SHIPPED_PRICE = SUM(ISNULL(BACKLOG_SHIPPED_PRICE,0.0)) FROM @TOP_RESULTS GROUP BY QUARTER_START, QUARTER_END	

	--INSERT INTO @TOP_RESULTS SELECT 'ROW', SUM(ISNULL(FC_PRICE,0.0)) FC_PRICE , SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) TOTAL, QUARTER_START, QUARTER_END, 1 FROM @RESULTS WHERE ODM NOT IN ( 'F5 Networks, Inc.','SonicWall','FLASH CHINA','SERCOMM CORPORATION','YAMAHA','Cisco','Aruba Networks Inc','FLEXTRONICS SHANGHAI','NetScaler, INC.','FURUKAWADENKI','JUNIPER NETWORKS','NOKIA INC.','TURBOCOMM','Array Networks','SYMANTEC','GIGABYTE','CAMEO COMMUNICATIONS','Huawei-3COM Technologies Co., LTD.','Huawei Technologies','SUN MICROSYSTEMS','MCAFEE','Fujitsu','Delta' ) GROUP BY QUARTER_START, QUARTER_END

	INSERT INTO @TOP_RESULTS SELECT 'ROW', (SUM(ISNULL(FC_PRICE,0.0)) - @TOP_FC_PRICE) FC_PRICE , (SUM(ISNULL(BL_PRICE,0)+ISNULL(ACTUAL_PRICE,0)) - @TOP_BACKLOG_SHIPPED_PRICE) TOTAL, QUARTER_START, QUARTER_END, 1 FROM @RESULTS GROUP BY QUARTER_START, QUARTER_END	

	--FOR RMA DETAILS
	DECLARE @RMAITEMCURSOR CURSOR
	SET @RMAITEMCURSOR = CURSOR FOR
	SELECT CUSTOMER_NAME , RLIT.PART_NUMBER , RMA_STATUS , RMA_REQUESTED_DATE , RT.CLOSE_DATE , QUANTITY , UNIT_PRICE, IS_CREDIT , RLIT.STATUS , RLIT.CLOSE_DATE,
	CASE WHEN ( RT.END_USER_ID IS NULL OR RT.END_USER_ID = '' OR RT.END_USER_ID = -1 ) THEN RT.CUSTOMER_NAME
	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = RT.END_USER_ID )
	END AS END_USER
	FROM RMA_TABLE RT, RMA_LINE_ITEM_TABLE RLIT, CUSTOMER CUST, PRODUCT P, PROCESS_STEP PS
	WHERE 	
	RT.RMA_ID = RLIT.RMA_ID 
	AND RMA_STATUS <> 'CANCELLED'
	AND RT.CUSTOMER_NAME = CUST.C_NAME 
	AND RLIT.PART_NUMBER = P.PART_NUMBER
	AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
	AND PS.PS_TYPE_NAME = 'FINISHED GOODS'

	-- RMA DETAILS CURSOR
	DECLARE @LINEITEMSTATUS VARCHAR(15)
	DECLARE @LINEITEMCLOSEDATE DATETIME	
	DECLARE @RMA_REQUESTED_DATE DATETIME
	DECLARE @RMA_CLOSE_DATE     DATETIME
	DECLARE @IS_CREDIT INT
	SET @IS_CREDIT = 1
	DECLARE @RMA_ENDUSER VARCHAR(50)		
	DECLARE @RMA_CUSTOMER_NAME VARCHAR(100)
	DECLARE @RMA_PART_NUMBER VARCHAR(100)
	DECLARE @RMA_STATUS VARCHAR(20)
	DECLARE @RMA_QUANTITY INT
	DECLARE @RMA_PRICE FLOAT
	DECLARE @AMOUNT FLOAT
	DECLARE @RMA_DATE DATETIME
	DECLARE @RMA_MONTH INT
	SET @QUARTER_START = NULL
	SET @QUARTER_END = NULL

	OPEN @RMAITEMCURSOR
	FETCH NEXT FROM @RMAITEMCURSOR INTO @RMA_CUSTOMER_NAME , @RMA_PART_NUMBER , @RMA_STATUS , @RMA_REQUESTED_DATE, @RMA_CLOSE_DATE , @RMA_QUANTITY , @RMA_PRICE, @IS_CREDIT , @LINEITEMSTATUS , @LINEITEMCLOSEDATE, @RMA_ENDUSER
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ( @LINEITEMSTATUS = 'CLOSE' )
		BEGIN
			SET @RMA_STATUS = 'CLOSE'
			SET @RMA_CLOSE_DATE = @LINEITEMCLOSEDATE
		END


		IF (@RMA_STATUS = 'ISSUED')
		BEGIN
			SET @RMA_DATE = CONVERT(DATETIME,CURRENT_TIMESTAMP)
			SET @RMA_STATUS = 'APPROVED'
		END
		ELSE IF (@RMA_STATUS = 'CLOSE')
		BEGIN
			SET @RMA_DATE = @RMA_CLOSE_DATE
			SET @RMA_STATUS = 'SHIPPED'
		END

		IF( @IS_CREDIT = 1)
		BEGIN
			SET @AMOUNT = 0
		END
		ELSE IF (@IS_CREDIT = 0)
		BEGIN
			SET @AMOUNT = @RMA_QUANTITY * @RMA_PRICE 
		END
		
		SET @RMA_MONTH = MONTH(@RMA_DATE)
		IF (@RMA_MONTH >=1 AND @RMA_MONTH <=3)
		BEGIN
			SET @QUARTER_START = 1
			SET @QUARTER_END =3
		END
		ELSE IF (@RMA_MONTH >=4 AND @RMA_MONTH <=6)
		BEGIN
			SET @QUARTER_START = 2
			SET @QUARTER_END =6
		END
		ELSE IF (@RMA_MONTH >=7 AND @RMA_MONTH <=9)
		BEGIN
			SET @QUARTER_START = 3
			SET @QUARTER_END =9
		END						
		ELSE IF (@RMA_MONTH >=10 AND @RMA_MONTH <=12)
		BEGIN
			SET @QUARTER_START = 4
			SET @QUARTER_END =12
		END

		INSERT INTO @RMA_RESULTS( RMA_PART_NUMBER, RMA_DATE, RMA_AMOUNT, RMA_STATUS, RMA_TYPE, RMA_QUANTITY, RMA_PRICE, RMA_END_CUSTOMER, QUARTER_START, QUARTER_END) VALUES( @RMA_PART_NUMBER, @RMA_DATE, @AMOUNT, @RMA_STATUS, 'RMA', @RMA_QUANTITY , @RMA_PRICE, @RMA_ENDUSER, @QUARTER_START, @QUARTER_END ) 		
		
		SET @AMOUNT = 0
		SET @IS_CREDIT = 1
		SET @RMA_MONTH = NULL
		
		SET @RMA_CUSTOMER_NAME = NULL
		SET @RMA_PART_NUMBER = NULL
		SET @RMA_STATUS = NULL
		SET @RMA_REQUESTED_DATE = NULL
		SET @RMA_CLOSE_DATE = NULL
		SET @RMA_QUANTITY = NULL
		SET @RMA_PRICE = NULL
		SET @LINEITEMSTATUS = NULL
		SET @LINEITEMCLOSEDATE = NULL
		SET @RMA_ENDUSER = NULL
		
		FETCH NEXT FROM @RMAITEMCURSOR INTO @RMA_CUSTOMER_NAME , @RMA_PART_NUMBER , @RMA_STATUS , @RMA_REQUESTED_DATE, @RMA_CLOSE_DATE , @RMA_QUANTITY , @RMA_PRICE, @IS_CREDIT , @LINEITEMSTATUS , @LINEITEMCLOSEDATE, @RMA_ENDUSER
	END	
		
	CLOSE @RMAITEMCURSOR
	DEALLOCATE @RMAITEMCURSOR


	INSERT INTO @TOP_RESULTS SELECT 'RMA', 0.0, SUM(ISNULL(RMA_AMOUNT,0.0)), QUARTER_START, QUARTER_END, 2 FROM @RMA_RESULTS WHERE DATEPART(QQ,RMA_DATE) = @QUARTER AND YEAR(RMA_DATE) = @YEAR  GROUP BY QUARTER_START, QUARTER_END	



	SELECT * FROM @TOP_RESULTS

 END
 
 







